package com.xinhe.web.reposity;

import com.xinhe.web.entity.AssetDetail;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.math.BigDecimal;
import java.util.List;

public interface AssetDetailRepository  extends JpaRepository<AssetDetail, Long> {
    @Query(value = "select ifnull(sum(trade_amount), 0) from tb_asset_detail where f_user_id=?1 and trade_type = 'ADOPTED_OIL_CARD' and asset_type = 'TEA_OIL_CARD' ", nativeQuery = true)
    BigDecimal sumTeaOilCard(Long userId);
    @Query(value = "select ifnull(sum(trade_amount), 0) from tb_asset_detail where f_user_id=?1 and trade_type = 'ADOPTED_CONSUMER_GOLD_RED_ENVELOPE' and asset_type = 'CONSUMER_GOLD_RED_ENVELOPE' ", nativeQuery = true)
    BigDecimal sumConsumerGoldRedEnvelope(Long userId);
    @Query(value = "select ifnull(sum(trade_amount), 0) from tb_asset_detail where f_user_id=?1 and trade_type = 'YUN_CHA_SEED' and asset_type = 'NEW_YUN_CHA_SEED' ", nativeQuery = true)
    BigDecimal sumYunChaSeed(Long userId);
    @Query(value = "select IFNULL(sum(remainder),0) from tb_asset_count where removed = 0 and type ='RMB' ", nativeQuery = true)
    BigDecimal findByAmountToWithdrawn();
    @Query(value = "select IFNULL(sum(trade_amount),0) from tb_asset_detail where removed = 0  and asset_type='TEA_OIL_CARD' and trade_type='ADOPTED_OIL_CARD'", nativeQuery = true)
    BigDecimal findByChaYouKa();
    @Query(value = "select IFNULL(sum(remainder),0) from tb_asset_count where removed = 0 and type ='TEA_OIL_CARD' ", nativeQuery = true)
    BigDecimal findByToBeTeaOilCard();
    @Query(value = "select IFNULL(sum(remainder),0) from tb_asset_count where removed = 0 and type ='UNFREEZING_CONSUMER_GOLD_RED_ENVELOPE' ", nativeQuery = true)
    BigDecimal findByHongBao();
    @Query(value = "select IFNULL(sum(remainder),0) from tb_asset_count where removed = 0 and type ='CONSUMER_GOLD_RED_ENVELOPE' ", nativeQuery = true)
    BigDecimal findByToBeConsumedHonBao();
    @Query(value = "select ifnull(sum(trade_amount),0) from tb_asset_detail where  trade_type='ADOPTED_TREE' and (asset_type='YUN_CHA_SEED' or asset_type='NEW_YUN_CHA_SEED')", nativeQuery = true)
    BigDecimal findBySumYunChaSeed();
    @Query(value = "select IFNULL(sum(remainder),0) from tb_asset_count where removed = 0 and (type ='YUN_CHA_SEED' or type ='NEW_YUN_CHA_SEED') ", nativeQuery = true)
    BigDecimal findByToBeYunChaSeed();
    @Query(value = "select IFNULL(sum(trade_amount),0) from tb_asset_detail where removed = 0 and f_user_id = ?1 and asset_type='RMB' and (trade_type='SYSTEM_THAW_YUN_CHA_SEED' or trade_type='THREE_THAW_YUN_CHA_SEED' or trade_type ='TWO_THAW_YUN_CHA_SEED' or trade_type ='RECOMMEND_FOUNDER_THAWING' or trade_type ='ADDITIONAL_YUN_CHA_SEED' ) ", nativeQuery = true)
    BigDecimal findByYunChaSend(Long userId);
    @Query(value = "select IFNULL(sum(trade_amount),0) from tb_asset_detail where removed = 0 and f_user_id = ?1 and asset_type='TEA_OIL' and (trade_type='ADOPTED_OIL_CARD_EXCHANGE_TEA_OIL')", nativeQuery = true)
    BigDecimal findByTeaOil(Long userId);
    @Query(value = "select IFNULL(sum(trade_amount),0) from tb_asset_detail where removed = 0 and f_user_id = ?1 and asset_type='TEA_OIL_CARD' and (trade_type='ADOPTED_OIL_CARD') ", nativeQuery = true)
    BigDecimal findByTeaOilCard(Long userId);

    @Query(value = "select id, f_user_id userId, asset_type assetType, trade_type tradeType, trade_amount tradeAmount, remainder,f_business_id businessId, create_time createTime,remark where removed=0 and trade_type ='REVENUE_SELLING_GOODS' and f_user_id=?1 ", nativeQuery = true)
    List<AssetDetail> findBySellerShopGoodsMoney(Long aLong);
}
